import sqlite3
from datetime import datetime, date, time

# 设置每小时停车费价格
PRICE = 1
# 设置停车场车位总数
NUMS = 100


# 注册日期/时间适配器和转换器（修复 decode 错误，Python 3 兼容）
def adapt_datetime(dt):
    return dt.isoformat()  # 将 datetime 对象转为 ISO 字符串（适配器不变）


def adapt_date(d):
    return d.isoformat()  # 将 date 对象转为 ISO 字符串（适配器不变）


def convert_datetime(s):
    if isinstance(s, bytes):
        s = s.decode()  # 如果是字节字符串，转换为普通字符串
    return datetime.fromisoformat(s)


def convert_date(s):
    if isinstance(s, bytes):
        s = s.decode()  # 如果是字节字符串，转换为普通字符串
    return date.fromisoformat(s)


sqlite3.register_adapter(datetime, adapt_datetime)
sqlite3.register_adapter(date, adapt_date)
sqlite3.register_converter("DATETIME", convert_datetime)
sqlite3.register_converter("DATE", convert_date)


# 定义初始化数据库函数
def init_db():
    conn = sqlite3.connect('parking.db', detect_types=sqlite3.PARSE_DECLTYPES)
    cursor = conn.cursor()

    # 创建月卡表（使用 DATE 类型）
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS month_cards (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        plate_number TEXT UNIQUE,
        owner_name TEXT,
        phone TEXT,
        start_date DATE,
        end_date DATE
    )
    ''')

    # 创建停车场记录表（使用 DATETIME 类型）
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS parking_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        plate_number TEXT,
        entry_time DATETIME,
        exit_time DATETIME,
        fee REAL,
        is_month_card INTEGER
    )
    ''')

    conn.commit()
    conn.close()


# 获取当前停车场内车辆数量
def get_current_vehicle_count():
    conn = sqlite3.connect('parking.db', detect_types=sqlite3.PARSE_DECLTYPES)
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM parking_records WHERE exit_time IS NULL")

    count = cursor.fetchone()[0]
    conn.close()
    return count


# 获取剩余车位数
def get_available_spots():
    return NUMS - get_current_vehicle_count()


# 按格式显示
def print_it(*args, width=14):
    formats = [f"{{:^{width}}}" for _ in args]
    format_str = "\t".join(formats)
    print(format_str.format(*args))


# 月卡管理函数
def monthcard():
    month_card = dict()
    month_card['车牌号码'] = input('请输入车牌号码：')
    month_card['车主姓名'] = input('请输入车主姓名：')

    while True:
        month_card['车主电话'] = input('请输入车主电话：')
        if len(month_card['车主电话']) == 11 and month_card['车主电话'].isdigit():
            break
        else:
            print('输入错误，电话号码必须为11位数字。')

    while True:
        start_date_str = input('请输入月卡开始日期：（格式为：XXXX-XX-XX）')
        try:
            month_card['开始日期'] = datetime.strptime(start_date_str, "%Y-%m-%d").date()
            break
        except Exception:
            print('日期格式错误...')

    while True:
        end_date_str = input('请输入月卡结束日期：（格式为：XXXX-XX-XX）')
        try:
            month_card['结束日期'] = datetime.strptime(end_date_str, "%Y-%m-%d").date()
            if month_card['结束日期'] > month_card['开始日期']:
                break
            else:
                print('月卡结束日期必须大于开始日期！')
        except Exception:
            print('日期格式错误...')

    try:
        conn = sqlite3.connect('parking.db', detect_types=sqlite3.PARSE_DECLTYPES)
        cursor = conn.cursor()

        cursor.execute('''
        INSERT INTO month_cards (plate_number, owner_name, phone, start_date, end_date)
        VALUES (?, ?, ?, ?, ?)
        ''', (month_card['车牌号码'], month_card['车主姓名'],
              month_card['车主电话'], month_card['开始日期'],
              month_card['结束日期']))

        conn.commit()
        print(f"月卡信息（车牌：{month_card['车牌号码']}）已成功录入！")

    except sqlite3.IntegrityError:
        print(f"错误：车牌 {month_card['车牌号码']} 已存在！")
    except Exception as e:
        print(f"数据库操作错误：{e}")
    finally:
        if conn:
            conn.close()


# 车辆入库管理函数
def inbound():
    available_spots = get_available_spots()
    if available_spots <= 0:
        print(f"停车场已满（总车位：{NUMS}），无法入库！")
        return

    plate_number = input("请输入入库车辆车牌号码：")

    try:
        conn = sqlite3.connect('parking.db', detect_types=sqlite3.PARSE_DECLTYPES)
        cursor = conn.cursor()

        cursor.execute("SELECT end_date FROM month_cards WHERE plate_number = ?", (plate_number,))
        month_card = cursor.fetchone()

        is_month_card = 1 if month_card else 0
        entry_time = datetime.now()

        cursor.execute('''
        INSERT INTO parking_records (plate_number, entry_time, is_month_card)
        VALUES (?, ?, ?)
        ''', (plate_number, entry_time, is_month_card))

        conn.commit()
        print(f"车辆 {plate_number} 入库成功！时间：{entry_time.strftime('%Y-%m-%d %H:%M:%S')}")
        print(f"当前停车场剩余车位：{available_spots - 1}/{NUMS}")

        if month_card:
            end_date = month_card[0]
            print(f"提示：该车辆为月卡用户，有效期至 {end_date.strftime('%Y-%m-%d')}")

    except Exception as e:
        print(f"入库登记错误：{e}")
    finally:
        if conn:
            conn.close()


# 车辆出库管理函数
def outbound():
    plate_number = input("请输入出库车辆车牌号码：")

    try:
        conn = sqlite3.connect('parking.db', detect_types=sqlite3.PARSE_DECLTYPES)
        cursor = conn.cursor()

        cursor.execute('''
        SELECT id, entry_time, is_month_card
        FROM parking_records
        WHERE plate_number = ? AND exit_time IS NULL
        ORDER BY entry_time DESC LIMIT 1
        ''', (plate_number,))

        record = cursor.fetchone()
        if not record:
            print(f"错误：未找到车辆 {plate_number} 的入库记录！")
            return

        record_id, entry_time, is_month_card = record
        exit_time = datetime.now()

        if is_month_card:
            cursor.execute('''
            SELECT end_date
            FROM month_cards
            WHERE plate_number = ?
            ''', (plate_number,))

            end_date = cursor.fetchone()[0]
            end_datetime = datetime.combine(end_date, time.max)

            if end_datetime >= exit_time:
                fee = 0
            elif end_datetime < entry_time:
                duration = (exit_time - entry_time).total_seconds() / 3600
                fee = round(duration * PRICE, 2)
            else:
                duration = (exit_time - end_datetime).total_seconds() / 3600
                fee = round(duration * PRICE, 2)
        else:
            duration = (exit_time - entry_time).total_seconds() / 3600
            fee = round(duration * PRICE, 2)

        cursor.execute('''
        UPDATE parking_records
        SET exit_time = ?, fee = ?
        WHERE id = ?
        ''', (exit_time, fee, record_id))

        conn.commit()

        print(f"车辆 {plate_number} 出库成功！")
        print(f"入库时间：{entry_time.strftime('%Y-%m-%d %H:%M:%S')}")
        print(f"出库时间：{exit_time.strftime('%Y-%m-%d %H:%M:%S')}")
        print(f"停车时长：{duration:.2f} 小时")
        print(f"应付费用：{fee} 元" if fee > 0 else "月卡用户，本次免费")
        print(f"当前停车场剩余车位：{get_available_spots()}/{NUMS}")

    except Exception as e:
        print(f"出库结算错误：{e}")
    finally:
        if conn:
            conn.close()


# 停车场状态查询函数
def query():
    while True:
        print("""
            1. 查询当前在场车辆
            2. 查询月卡用户列表
            3. 查询历史收费记录
            4. 查询剩余车位
            0. 返回上级菜单
            """)
        sub_choice = input("请选择查询类型：")

        try:
            conn = sqlite3.connect('parking.db', detect_types=sqlite3.PARSE_DECLTYPES)
            cursor = conn.cursor()

            if sub_choice == '1':
                cursor.execute('''
                    SELECT pr.plate_number, pr.entry_time, mc.end_date
                    FROM parking_records pr
                    LEFT JOIN month_cards mc ON pr.plate_number = mc.plate_number
                    WHERE pr.exit_time IS NULL
                    ORDER BY pr.entry_time
                    ''')

                records = cursor.fetchall()
                available_spots = get_available_spots()

                print(f"\n当前停车场状态：总车位 {NUMS}，剩余车位 {available_spots}")
                if not records:
                    print("当前停车场没有车辆在场。")
                else:
                    print("\n当前在场车辆：")
                    print_it('车牌号码', '入库时间', '月卡信息', width=16)
                    for record in records:
                        plate, entry_time, end_date = record
                        if end_date:
                            if end_date >= date.today():
                                card_info = f"月卡到期：{end_date.strftime('%Y-%m-%d')}"
                            else:
                                card_info = "月卡已过期"
                        else:
                            card_info = "临时停车"

                        print_it(plate, entry_time.strftime('%Y-%m-%d %H:%M:%S'), card_info, width=16)

            elif sub_choice == '2':
                cursor.execute('''
                    SELECT plate_number, owner_name, phone, start_date, end_date
                    FROM month_cards
                    ORDER BY end_date DESC
                    ''')

                cards = cursor.fetchall()
                if not cards:
                    print("暂无月卡用户记录。")
                else:
                    print("\n月卡用户列表：")
                    print_it("车牌号码", "车主姓名", "联系电话", "开始日期", "结束日期", width=16)
                    for card in cards:
                        plate, owner, phone, start_date, end_date = card
                        print_it(plate, owner, phone,
                                 start_date.strftime('%Y-%m-%d'),
                                 end_date.strftime('%Y-%m-%d'),
                                 width=16)

            elif sub_choice == '3':
                cursor.execute('''
                    SELECT pr.plate_number, mc.owner_name, pr.entry_time, pr.exit_time, pr.fee
                    FROM parking_records pr
                    LEFT JOIN month_cards mc ON pr.plate_number = mc.plate_number
                    WHERE pr.exit_time IS NOT NULL AND pr.fee > 0
                    ORDER BY pr.exit_time DESC
                    LIMIT 50
                    ''')

                fees = cursor.fetchall()
                if not fees:
                    print("暂无收费记录。")
                else:
                    print("\n最近收费记录（最多显示50条）：")
                    print_it("车牌号码", "车主姓名", "入库时间", "出库时间", "费用(元)", width=16)
                    for fee in fees:
                        plate, owner, entry, exit_time, amount = fee
                        owner = owner if owner else "未知"
                        print_it(plate, owner,
                                 entry.strftime('%Y-%m-%d %H:%M:%S'),
                                 exit_time.strftime('%Y-%m-%d %H:%M:%S'),
                                 f"{amount:.2f}",
                                 width=16)

            elif sub_choice == '4':
                available = get_available_spots()
                print(f"\n当前停车场总车位：{NUMS}")
                print(f"剩余车位：{available}")
                print(f"占用车位：{NUMS - available}")
                if available < 10:
                    print("⚠️ 注意：剩余车位不足10个！")

            elif sub_choice == '0':
                break

            else:
                print("无效的查询类型选择！")

        except Exception as e:
            print(f"查询错误：{e}")
        finally:
            if conn:
                conn.close()


if __name__ == '__main__':
    init_db()

    while True:
        choose = input("""
            1、月卡车辆录入
            2、车辆入库登记
            3、车辆出库结算
            4、停车场状态查询
            0、退出系统

            请输入您的选择：
            """)
        if choose in ('0', '1', '2', '3', '4'):
            match choose:
                case '0':
                    break
                case '1':
                    monthcard()
                case '2':
                    inbound()
                case '3':
                    outbound()
                case '4':
                    query()
        else:
            print('输入错误，请重新输入!')